package com.yangjun.dao.impl.stockAnalysis;

import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class StockAnalysisDao {
	@Autowired
	@Resource(name = "bikadJdbcTemplate")
	private JdbcTemplate _jdbct;

	// public List<Map<String, Object>> getOverView() {
	// String sql = "";
	// sql += "select * from viswh_org_wh_stock_live_v";
	//
	// return _jdbct.queryForList(sql);
	// }

	public List<Map<String, Object>> getOverView() {
		String sql = "";
		sql += "select t.skuqty,";
		sql += "       t.skuqty_dull,";
		sql += "       t.batchqty,";
		sql += "       t.batchqty_dull,";
		sql += "       trunc(t.stockamt) stockamt,";
		sql += "       trunc(t.stockamt_dull) stockamt_dull,";
		sql += "       t.stockqty,";
		sql += "       t.stockqty_dull,";
		sql += "       trunc(t.turnover_days) turnover_days,";
		sql += "       trunc(t.turnover_days_dull) turnover_days_dull,";
		sql += "       t.onwayqty,";
		sql += "       trunc(t.onwayamt) onwayamt, t.onwaysku";
		sql += "  from bikad.viswh_org_wh_stock_live t";
		sql += " where t.data_date = trunc(sysdate) and t.whcode ='-1'";
		// System.out.println(sql);
		return _jdbct.queryForList(sql);
	}

	public List<Map<String, Object>> getWhStockQty() {
		String sql = "";
		sql += "select t.*,";
		sql += "       ceil(decode(toatalstockamt, 0, 0, stockamt / toatalstockamt)) as stockamt_rate";
		sql += "  from (select t.data_date,";
		sql += "               t.whcode,";
		sql += "               w.whname,";
		sql += "               t.skuqty,";
		sql += "               t.batchqty,";
		sql += "               trunc(t.stockamt) as stockamt,";
		sql += "               t.stockqty,";
		sql += "               ceil(sum(t.stockamt) over(partition by t.data_date)) as toatalstockamt";
		sql += "          from viswh_org_wh_stock_live t, wms.bd_whbaseinfo w";
		sql += "         where t.whcode = w.whcode(+)";
		sql += "           and t.whcode <> '-1'";
		sql += "           and t.orgcode = '001'";
		sql += "           and t.data_date =";
		sql += "               (select max(data_date) from viswh_org_wh_stock_live)) t ";
		sql += "order by stockamt ";

		return _jdbct.queryForList(sql);
	}

	public List<Map<String, Object>> getCATStock() {
		String sql = "  select to_char (data_date,'mm-dd')as data_date,";
		sql += "catname,";
		sql += "stockqty,";
		sql += "trunc(stockamt) as stockamt,";
		sql += "batchqty,";
		sql += "skuqty,";
		sql += "trunc(turnover_days) as turnover_days from viswh_cat_stock where 1=1  and data_date=trunc(sysdate)";
		sql += " order by stockamt";
		//System.err.println(sql);
		return _jdbct.queryForList(sql);
	}

	public List<Map<String, Object>> getCATStock(String catname) {
		String sql = "  select to_char (data_date,'mm-dd')as data_date,";
		sql += "catname,";
		sql += "stockqty,";
		sql += "trunc(stockamt) as stockamt,";
		sql += "batchqty,";
		sql += "skuqty,";
		sql += "trunc(turnover_days) as turnover_days from viswh_cat_stock where 1=1  ";
		sql += " and data_date between trunc(sysdate-30) and trunc(sysdate-1)";
		sql += " and catname='" + catname + "'";
		sql += " order by data_date,catname";
		//System.err.println(sql);
		return _jdbct.queryForList(sql);
	}

	public List<Map<String, Object>> getCATList() {
		String sql = "select distinct t.catname  from viswh_cat_stock t";
		return _jdbct.queryForList(sql);
	}

	public List<Map<String, Object>> getWhStockHis(String whcode, String orderby) {
//visp_dim_serial_day 可以被visc_cons_time_dim_d替换
		StringBuffer sql = new StringBuffer(
				"select to_char(dt.data_date,'mm-dd') as data_date,"
						+ "       t.whcode,"
						+ "       w.whname,"
						+ "       nvl(t.skuqty,0) skuqty,"
						+ "       nvl(t.batchqty,0) batchqty,"
						+ "       nvl(ceil(t.stockamt),0) as stockamt,"
						+ "       nvl(t.stockqty,0) stockqty,"
						+ "       nvl(ceil(t.turnover_days),0) as turnover_days"
						+ "   from viswh_org_wh_stock_live t, wms.bd_whbaseinfo w , visp_dim_serial_day dt"
						+ "   where t.whcode = w.whcode(+)"
						+ "   and t.whcode(+) = :whcode"
						+ "   and t.orgcode(+) = '001'"
						+ "   and t.data_date(+)=dt.data_date and dt.data_date between trunc(sysdate-31) and trunc(sysdate-1)"
						+ "   order by data_date");
		NamedParameterJdbcTemplate njdbct = new NamedParameterJdbcTemplate(
				_jdbct);
		MapSqlParameterSource parms = new MapSqlParameterSource().addValue(
				"whcode", whcode);
		return njdbct.queryForList(sql.toString(), parms);
	}

	public List<Map<String, Object>> getDullSale(String whcode, String orderby) {
		String sql = "";
		sql += "select to_char(t.data_date,'mm-dd') as data_date,";
		sql += "       sum(t.skuqty) as skuqty,";
		sql += "       sum(t.batchqty) as batchqty,";
		sql += "       trunc(sum(t.stockamt)) as stockamt,";
		sql += "       sum(t.stockqty) as stockqty";
		sql += "  from viswh_org_dull t";
		sql += " where t.data_date between trunc(sysdate - 31) and trunc(sysdate - 1)";
		sql += " group by data_date";
		sql += " order by t.data_date";
		NamedParameterJdbcTemplate _njdbct = new NamedParameterJdbcTemplate(
				_jdbct);
		MapSqlParameterSource parms = new MapSqlParameterSource().addValue(
				"whcode", whcode);
		// System.err.println(sql);
		return _njdbct.queryForList(sql, parms);
	}

	public List<Map<String, Object>> getInventory(String whcode, String orderby) {
		String sql = "";
		sql += "select data_date, whcode, overageamount, shortageamount";
		sql += "  from visp_place_ware_inventory t";
		sql += " where t.whcode = :whcode";
		sql += "   and t.placecode = '-1'";
		sql += "   and t.data_date >=";
		sql += "       (select max(data_date) - 30 from visp_place_ware_inventory)";
		sql += "   order by " + orderby;
		NamedParameterJdbcTemplate _njdbct = new NamedParameterJdbcTemplate(
				_jdbct);
		MapSqlParameterSource parms = new MapSqlParameterSource().addValue(
				"whcode", whcode);
		return _njdbct.queryForList(sql, parms);
	}

	public List<Map<String, Object>> getPSS() {
		String sql = "";
		sql += "select t.*,";
		sql += "       (salesrecount + purincount + alloincount + wholesalerecount) as inqty,";
		sql += "       (salesreamount + purinamount + alloinamount + t.wholesalereamount) as inamt,";
		sql += "       (salescount + purrecount + allooutcount + wholesalecount) as outqty,";
		sql += "       (salesamount + purreamount + allooutamount + t.wholesaleamount) as outamt";
		sql += "  from (select to_char(dt.data_date, 'mm-dd') as data_date,";
		sql += "               trunc(sum(nvl(salesrecount, 0))) as salesrecount, ";
		sql += "               trunc(sum(nvl(salesreamount, 0))) as salesreamount, ";
		sql += "               trunc(sum(nvl(purincount, 0))) as purincount, ";
		sql += "               trunc(sum(nvl(purinamount, 0))) as purinamount, ";
		sql += "               trunc(sum(nvl(alloincount, 0))) as alloincount, ";
		sql += "               trunc(sum(nvl(alloinamount, 0))) as alloinamount, ";
		sql += "               trunc(sum(nvl(wholesalerecount, 0))) as wholesalerecount, ";
		sql += "               trunc(sum(nvl(wholesalereamount, 0))) as wholesalereamount, ";
		sql += "               trunc(sum(nvl(salescount, 0))) as salescount, ";
		sql += "               trunc(sum(nvl(salesamount, 0))) as salesamount, ";
		sql += "               trunc(sum(nvl(purrecount, 0))) as purrecount, ";
		sql += "               trunc(sum(nvl(purreamount, 0))) as purreamount, ";
		sql += "               trunc(sum(nvl(allooutcount, 0))) as allooutcount, ";
		sql += "               trunc(sum(nvl(allooutamount, 0))) as allooutamount, ";
		sql += "               trunc(sum(nvl(wholesalecount, 0))) as wholesalecount, ";
		sql += "               trunc(sum(nvl(wholesaleamount, 0))) as wholesaleamount, ";
		sql += "               trunc(sum(nvl(t.closeamount, 0))) as stockamount,";
		sql += "               trunc(sum(nvl(t.closecount, 0))) as stockqty";
		sql += "          from viswh_org_pss t, visp_dim_serial_day dt";
		sql += "         where t.data_date(+) = dt.data_date";
		sql += "           and dt.data_date between trunc(sysdate - 31) and";
		sql += "               trunc(sysdate - 1)";
		sql += "         group by dt.data_date";
		sql += "         order by dt.data_date) t";
		return _jdbct.queryForList(sql);
	}
//visp_dim_serial_day 可以被visc_cons_time_dim_d替换
	public List<Map<String, Object>> getAllocate(String shopcode) {
		String sql = "";
		sql += "select to_char(dt.data_date,'mm-dd') as data_date,whcode,whname,shopcode,shopname,nvl(allocoutqty,0)allocoutqty,nvl(allocinqty,0)allocinqty,nvl(allocoutamt,0)allocoutamt,nvl(allocinamt,0) allocinamt";
		sql += ",nvl(batchoutqty,0) batchoutqty,nvl(batchinqty,0) batchinqty,nvl(skuoutqty,0) skuoutqty,nvl(skuinqty,0) skuinqty";
		sql += "  from (select data_date,";
		sql += "               nvl(whcode, '-1') whcode,";
		sql += "               nvl(whname, '-1') whname,";
		sql += "               nvl(shopcode, '-1') shopcode,";
		sql += "               nvl(shopname, '-1') shopname,";
		sql += "               sum(allocoutqty) allocoutqty,";
		sql += "               sum(allocinqty) allocinqty,";
		sql += "               round(sum(allocoutamt), 2) as allocoutamt,";
		sql += "               round(sum(allocinamt), 2) as allocinamt,";
		sql += "               sum(batchoutqty) batchoutqty,";
		sql += "               sum(batchinqty) batchinqty,";
		sql += "               sum(skuoutqty) skuoutqty,";
		sql += "               sum(skuinqty) skuinqty";
		sql += "          from viswh_org_store_allocate t";
		sql += "         group by grouping sets((data_date, whcode, whname, shopcode, shopname),(data_date))) t,visp_dim_serial_day dt";
		sql += " where shopcode(+) = :shopcode";
		sql += "   and t.data_date(+) = dt.data_date and dt.data_date between trunc(sysdate-31) and trunc(sysdate-1)";
		sql += "   order by  dt.data_date";
		NamedParameterJdbcTemplate _njdbct = new NamedParameterJdbcTemplate(
				_jdbct);
		MapSqlParameterSource parms = new MapSqlParameterSource().addValue(
				"shopcode", shopcode);
		return _njdbct.queryForList(sql, parms);
	}//visp_dim_serial_day 可以被visc_cons_time_dim_d替换

	public List<Map<String, Object>> getBatch() {
		String sql = "";
		sql += "select batchtype, orderno, count(distinct t.warecode) as skuqty";
		sql += "  from viswh_ware_bath t";
		sql += " group by t.batchtype, t.orderno";
		sql += " order by orderno";
		return _jdbct.queryForList(sql);
	}

	public List<Map<String, Object>> getShop() {
		String sql = "";
		sql += "select * from viswh_dim_shop";
		return _jdbct.queryForList(sql);
	}

}
